{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import time\n",
    "import glob\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime\n",
    "import math\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('max_columns', 30)\n",
    "pd.options.display.float_format = '{:20,.2f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#kick = pd.DataFrame()\n",
    "\n",
    "#for file in glob.glob(\"C:/Users/sastu/Downloads/Kickstarter_2018-10-18T03_20_48_880Z/*.csv\"):\n",
    "#    df = pd.read_csv(file)\n",
    "#    kick = kick.append(df)\n",
    "#    print(\"File appended: \" + file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#kick.to_csv(\"C:/Users/sastu/Downloads/Kick_data_appended.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k = pd.read_csv(r\"C:\\OSU\\Sem3\\BAN 5753\\SAS Symposium\\Kick_data_appended.csv\", encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'backers_count', 'blurb', 'category',\n",
       "       'converted_pledged_amount', 'country', 'created_at', 'creator',\n",
       "       'currency', 'currency_symbol', 'currency_trailing_code',\n",
       "       'current_currency', 'deadline', 'disable_communication', 'friends',\n",
       "       'fx_rate', 'goal', 'id', 'is_backing', 'is_starrable', 'is_starred',\n",
       "       'launched_at', 'location', 'name', 'permissions', 'photo', 'pledged',\n",
       "       'profile', 'slug', 'source_url', 'spotlight', 'staff_pick', 'state',\n",
       "       'state_changed_at', 'static_usd_rate', 'urls', 'usd_pledged',\n",
       "       'usd_type'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#k.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k = k[['id', 'created_at','state_changed_at']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k.created_at = pd.to_datetime(k.created_at, unit='s')\n",
    "#k.state_changed_at = pd.to_datetime(k.state_changed_at, unit='s')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k['created_date'] = k.created_at.dt.date\n",
    "#k['state_changed_date'] = k.state_changed_at.dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k = k.drop(['created_at', 'state_changed_at'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [],
   "source": [
    "#k.columns = ['project_id', 'created_date', 'state_changed_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "#kick = pd.merge(kick, k, on=['project_id'], how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "#kick.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read the data from Kaggle and Webroots websites\n",
    "\n",
    "### 1. Join the data on 'id' column\n",
    "### 2. Keep useful columns from both the datasets (Dataset acquired from Kaggle is used as a primary dataset and some of the columns from the dataset acquired from Web Robots have added in the primary dataset)\n",
    "### 3. Use 'static_usd_rate' column to convert 'usd_pledged' and 'goal_amount' columns\n",
    "### 4. Keep only 'successful' and 'failed' projects based on 'project_state' column\n",
    "### 5. Calculate 'campaign_length' column using 'launched' and 'deadline' columns\n",
    "\n",
    "## Finally various features were engineered for my analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.read_csv(r\"C:\\OSU\\Sem3\\BAN 5753\\SAS Symposium\\kickstarter_data_model.csv\", encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['project_id', 'project_name', 'project_blurb', 'main_category',\n",
       "       'sub_category', 'project_state', 'country', 'deadline', 'launched',\n",
       "       'campaign_length', 'backers_count', 'currency', 'static_usd_rate',\n",
       "       'pledged_amount', 'pledged_amount_usd', 'goal_amount',\n",
       "       'goal_amount_usd', 'staff_pick', 'spotlight', 'disable_communication'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kick.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launched'] = pd.to_datetime(kick.launched)\n",
    "kick['deadline'] = pd.to_datetime(kick.deadline)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launch_year'] = pd.DatetimeIndex(kick['launched']).year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launch_month'] = pd.DatetimeIndex(kick['launched']).month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launch_week'] = pd.DatetimeIndex(kick['launched']).week"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launch_day'] = kick['launched'].dt.day_name()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['launch_quarter'] = kick['launched'].dt.quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['campaign_less_than_1_month'] = np.where(kick.campaign_length<=31, 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "kick['goal_amount_less_than_75'] = np.where(kick.goal_amount_usd<=12500, 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['project_blurb_length'] = kick.project_blurb.str.len()\n",
    "kick['project_name_length'] = kick.project_name.str.len()\n",
    "# presence of !\n",
    "kick['project_name_exclaim'] = (kick.project_name.str[-1] == '!').astype(int)\n",
    "\n",
    "# presence of ?\n",
    "kick['project_name_question'] = (kick.project_name.str[-1] == '?').astype(int)\n",
    "\n",
    "# number of words in the name\n",
    "kick['project_name_words'] = kick.project_name.apply(lambda x: len(str(x).split(' ')))\n",
    "\n",
    "# if name is uppercase\n",
    "kick['project_name_is_upper'] = kick.project_name.str.isupper().astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['project_blurb_words'] = kick.project_blurb.apply(lambda x: len(str(x).split(' ')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['goal_amount_category_perc'] =  kick.groupby(['main_category'])['goal_amount_usd'].transform(\n",
    "                     lambda x: pd.qcut(x, [0, .35, .70, 1.0], labels =[1,2,3]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['campaign_length_category_perc'] =  kick.groupby(['main_category'])['campaign_length'].transform(\n",
    "                     lambda x: pd.qcut(x, [0, .35, .70, 1.0], labels =False, duplicates='drop'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "#creating a metric to see number of competitors for a given project in a given quarter\n",
    "#number of participants in a given category, that launched in the same year and quarter and in the same goal bucket\n",
    "ks_particpants_qtr = kick.groupby(['main_category','launch_year','launch_quarter','goal_amount_category_perc']).count()\n",
    "ks_particpants_qtr=ks_particpants_qtr[['project_name']]\n",
    "#since the above table has all group by columns created as index, converting them into columns\n",
    "ks_particpants_qtr.reset_index(inplace=True)\n",
    "ks_particpants_qtr.rename(index=str, columns={\"project_name\": \"competitors_quarter\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>main_category</th>\n",
       "      <th>launch_year</th>\n",
       "      <th>launch_month</th>\n",
       "      <th>goal_amount_category_perc</th>\n",
       "      <th>competitors_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>4</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>6</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>6</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>7</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>7</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>8</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>8</td>\n",
       "      <td>2.00</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>8</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>9</td>\n",
       "      <td>2.00</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>9</td>\n",
       "      <td>3.00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>10</td>\n",
       "      <td>1.00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>10</td>\n",
       "      <td>2.00</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>10</td>\n",
       "      <td>3.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>11</td>\n",
       "      <td>1.00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>11</td>\n",
       "      <td>2.00</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>11</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>2.00</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>2.00</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>1.00</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>2.00</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Art</td>\n",
       "      <td>2010</td>\n",
       "      <td>3</td>\n",
       "      <td>1.00</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4340</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>2.00</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4341</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>3</td>\n",
       "      <td>3.00</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4342</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>1.00</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4343</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>2.00</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4344</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>4</td>\n",
       "      <td>3.00</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4345</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4346</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4347</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>5</td>\n",
       "      <td>3.00</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4348</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>1.00</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4349</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>2.00</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4350</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>3.00</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4351</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>1.00</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4352</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>2.00</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4353</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>7</td>\n",
       "      <td>3.00</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4354</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>1.00</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4355</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>2.00</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4356</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>3.00</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4357</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>1.00</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4358</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>2.00</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4359</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>3.00</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4360</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>1.00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4361</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>2.00</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4362</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>3.00</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4363</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>1.00</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4364</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>2.00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4365</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>3.00</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4366</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>1.00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4367</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>2.00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4368</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>3.00</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4369</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4370 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     main_category  launch_year  launch_month  goal_amount_category_perc  \\\n",
       "0              Art         2009             4                       1.00   \n",
       "1              Art         2009             5                       1.00   \n",
       "2              Art         2009             5                       2.00   \n",
       "3              Art         2009             6                       1.00   \n",
       "4              Art         2009             6                       2.00   \n",
       "5              Art         2009             6                       3.00   \n",
       "6              Art         2009             7                       1.00   \n",
       "7              Art         2009             7                       2.00   \n",
       "8              Art         2009             8                       1.00   \n",
       "9              Art         2009             8                       2.00   \n",
       "10             Art         2009             8                       3.00   \n",
       "11             Art         2009             9                       1.00   \n",
       "12             Art         2009             9                       2.00   \n",
       "13             Art         2009             9                       3.00   \n",
       "14             Art         2009            10                       1.00   \n",
       "15             Art         2009            10                       2.00   \n",
       "16             Art         2009            10                       3.00   \n",
       "17             Art         2009            11                       1.00   \n",
       "18             Art         2009            11                       2.00   \n",
       "19             Art         2009            11                       3.00   \n",
       "20             Art         2009            12                       1.00   \n",
       "21             Art         2009            12                       2.00   \n",
       "22             Art         2009            12                       3.00   \n",
       "23             Art         2010             1                       1.00   \n",
       "24             Art         2010             1                       2.00   \n",
       "25             Art         2010             1                       3.00   \n",
       "26             Art         2010             2                       1.00   \n",
       "27             Art         2010             2                       2.00   \n",
       "28             Art         2010             2                       3.00   \n",
       "29             Art         2010             3                       1.00   \n",
       "...            ...          ...           ...                        ...   \n",
       "4340       Theater         2017             3                       2.00   \n",
       "4341       Theater         2017             3                       3.00   \n",
       "4342       Theater         2017             4                       1.00   \n",
       "4343       Theater         2017             4                       2.00   \n",
       "4344       Theater         2017             4                       3.00   \n",
       "4345       Theater         2017             5                       1.00   \n",
       "4346       Theater         2017             5                       2.00   \n",
       "4347       Theater         2017             5                       3.00   \n",
       "4348       Theater         2017             6                       1.00   \n",
       "4349       Theater         2017             6                       2.00   \n",
       "4350       Theater         2017             6                       3.00   \n",
       "4351       Theater         2017             7                       1.00   \n",
       "4352       Theater         2017             7                       2.00   \n",
       "4353       Theater         2017             7                       3.00   \n",
       "4354       Theater         2017             8                       1.00   \n",
       "4355       Theater         2017             8                       2.00   \n",
       "4356       Theater         2017             8                       3.00   \n",
       "4357       Theater         2017             9                       1.00   \n",
       "4358       Theater         2017             9                       2.00   \n",
       "4359       Theater         2017             9                       3.00   \n",
       "4360       Theater         2017            10                       1.00   \n",
       "4361       Theater         2017            10                       2.00   \n",
       "4362       Theater         2017            10                       3.00   \n",
       "4363       Theater         2017            11                       1.00   \n",
       "4364       Theater         2017            11                       2.00   \n",
       "4365       Theater         2017            11                       3.00   \n",
       "4366       Theater         2017            12                       1.00   \n",
       "4367       Theater         2017            12                       2.00   \n",
       "4368       Theater         2017            12                       3.00   \n",
       "4369       Theater         2018             1                       3.00   \n",
       "\n",
       "      competitors_month  \n",
       "0                     1  \n",
       "1                     5  \n",
       "2                     5  \n",
       "3                     2  \n",
       "4                     2  \n",
       "5                     1  \n",
       "6                     5  \n",
       "7                     4  \n",
       "8                     2  \n",
       "9                     7  \n",
       "10                    2  \n",
       "11                   15  \n",
       "12                   15  \n",
       "13                   11  \n",
       "14                    8  \n",
       "15                    6  \n",
       "16                    2  \n",
       "17                    9  \n",
       "18                    7  \n",
       "19                    5  \n",
       "20                    5  \n",
       "21                    6  \n",
       "22                    3  \n",
       "23                    6  \n",
       "24                   12  \n",
       "25                    5  \n",
       "26                   12  \n",
       "27                    8  \n",
       "28                    4  \n",
       "29                   10  \n",
       "...                 ...  \n",
       "4340                 16  \n",
       "4341                 17  \n",
       "4342                 22  \n",
       "4343                 23  \n",
       "4344                 24  \n",
       "4345                 19  \n",
       "4346                 20  \n",
       "4347                 30  \n",
       "4348                 27  \n",
       "4349                 33  \n",
       "4350                 23  \n",
       "4351                 19  \n",
       "4352                 20  \n",
       "4353                 22  \n",
       "4354                 20  \n",
       "4355                 15  \n",
       "4356                 22  \n",
       "4357                 14  \n",
       "4358                 14  \n",
       "4359                 19  \n",
       "4360                  9  \n",
       "4361                 13  \n",
       "4362                 23  \n",
       "4363                 10  \n",
       "4364                 11  \n",
       "4365                 20  \n",
       "4366                 11  \n",
       "4367                 11  \n",
       "4368                 11  \n",
       "4369                  1  \n",
       "\n",
       "[4370 rows x 5 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#creating a metric to see number of competitors for a given project in a given month\n",
    "#number of participants in a given category, that launched in the same year and month and in the same goal bucket\n",
    "ks_particpants_mth=kick.groupby(['main_category','launch_year','launch_month','goal_amount_category_perc']).count()\n",
    "ks_particpants_mth=ks_particpants_mth[['project_name']]\n",
    "#since the above table has all group by columns created as index, converting them into columns\n",
    "ks_particpants_mth.reset_index(inplace=True)\n",
    "ks_particpants_mth.rename(index=str, columns={\"project_name\": \"competitors_month\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>main_category</th>\n",
       "      <th>launch_year</th>\n",
       "      <th>launch_week</th>\n",
       "      <th>goal_amount_category_perc</th>\n",
       "      <th>competitors_week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>17</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>18</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>19</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>19</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>20</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>20</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>21</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>22</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>23</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>23</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>24</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>24</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>27</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>28</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>29</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>29</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>30</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>30</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>31</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>31</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>32</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>32</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>33</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>35</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>36</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>36</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>36</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>37</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>37</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Art</td>\n",
       "      <td>2009</td>\n",
       "      <td>37</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17040</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>42</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17041</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>42</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17042</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>43</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17043</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>43</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17044</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>43</td>\n",
       "      <td>3.00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17045</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>44</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17046</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>44</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17047</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>45</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17048</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>45</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17049</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>46</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17050</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>46</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17051</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>46</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17052</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>47</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17053</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>47</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17054</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>47</td>\n",
       "      <td>3.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17055</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>48</td>\n",
       "      <td>1.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17056</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>48</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17057</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>48</td>\n",
       "      <td>3.00</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17058</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>49</td>\n",
       "      <td>1.00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17059</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>49</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17060</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>49</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17061</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>50</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17062</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>50</td>\n",
       "      <td>2.00</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17063</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>50</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17064</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>51</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17065</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>51</td>\n",
       "      <td>2.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17066</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>51</td>\n",
       "      <td>3.00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17067</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>52</td>\n",
       "      <td>2.00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17068</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2017</td>\n",
       "      <td>52</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17069</th>\n",
       "      <td>Theater</td>\n",
       "      <td>2018</td>\n",
       "      <td>1</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>17070 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      main_category  launch_year  launch_week  goal_amount_category_perc  \\\n",
       "0               Art         2009           17                       1.00   \n",
       "1               Art         2009           18                       2.00   \n",
       "2               Art         2009           19                       1.00   \n",
       "3               Art         2009           19                       2.00   \n",
       "4               Art         2009           20                       1.00   \n",
       "5               Art         2009           20                       2.00   \n",
       "6               Art         2009           21                       1.00   \n",
       "7               Art         2009           22                       2.00   \n",
       "8               Art         2009           23                       2.00   \n",
       "9               Art         2009           23                       3.00   \n",
       "10              Art         2009           24                       1.00   \n",
       "11              Art         2009           24                       2.00   \n",
       "12              Art         2009           27                       2.00   \n",
       "13              Art         2009           28                       1.00   \n",
       "14              Art         2009           29                       1.00   \n",
       "15              Art         2009           29                       2.00   \n",
       "16              Art         2009           30                       1.00   \n",
       "17              Art         2009           30                       2.00   \n",
       "18              Art         2009           31                       1.00   \n",
       "19              Art         2009           31                       3.00   \n",
       "20              Art         2009           32                       2.00   \n",
       "21              Art         2009           32                       3.00   \n",
       "22              Art         2009           33                       1.00   \n",
       "23              Art         2009           35                       2.00   \n",
       "24              Art         2009           36                       1.00   \n",
       "25              Art         2009           36                       2.00   \n",
       "26              Art         2009           36                       3.00   \n",
       "27              Art         2009           37                       1.00   \n",
       "28              Art         2009           37                       2.00   \n",
       "29              Art         2009           37                       3.00   \n",
       "...             ...          ...          ...                        ...   \n",
       "17040       Theater         2017           42                       2.00   \n",
       "17041       Theater         2017           42                       3.00   \n",
       "17042       Theater         2017           43                       1.00   \n",
       "17043       Theater         2017           43                       2.00   \n",
       "17044       Theater         2017           43                       3.00   \n",
       "17045       Theater         2017           44                       2.00   \n",
       "17046       Theater         2017           44                       3.00   \n",
       "17047       Theater         2017           45                       1.00   \n",
       "17048       Theater         2017           45                       3.00   \n",
       "17049       Theater         2017           46                       1.00   \n",
       "17050       Theater         2017           46                       2.00   \n",
       "17051       Theater         2017           46                       3.00   \n",
       "17052       Theater         2017           47                       1.00   \n",
       "17053       Theater         2017           47                       2.00   \n",
       "17054       Theater         2017           47                       3.00   \n",
       "17055       Theater         2017           48                       1.00   \n",
       "17056       Theater         2017           48                       2.00   \n",
       "17057       Theater         2017           48                       3.00   \n",
       "17058       Theater         2017           49                       1.00   \n",
       "17059       Theater         2017           49                       2.00   \n",
       "17060       Theater         2017           49                       3.00   \n",
       "17061       Theater         2017           50                       1.00   \n",
       "17062       Theater         2017           50                       2.00   \n",
       "17063       Theater         2017           50                       3.00   \n",
       "17064       Theater         2017           51                       1.00   \n",
       "17065       Theater         2017           51                       2.00   \n",
       "17066       Theater         2017           51                       3.00   \n",
       "17067       Theater         2017           52                       2.00   \n",
       "17068       Theater         2017           52                       3.00   \n",
       "17069       Theater         2018            1                       3.00   \n",
       "\n",
       "       competitors_week  \n",
       "0                     1  \n",
       "1                     1  \n",
       "2                     1  \n",
       "3                     1  \n",
       "4                     3  \n",
       "5                     2  \n",
       "6                     1  \n",
       "7                     1  \n",
       "8                     1  \n",
       "9                     1  \n",
       "10                    2  \n",
       "11                    1  \n",
       "12                    1  \n",
       "13                    2  \n",
       "14                    1  \n",
       "15                    1  \n",
       "16                    1  \n",
       "17                    2  \n",
       "18                    1  \n",
       "19                    1  \n",
       "20                    2  \n",
       "21                    1  \n",
       "22                    2  \n",
       "23                    3  \n",
       "24                    1  \n",
       "25                    2  \n",
       "26                    1  \n",
       "27                    3  \n",
       "28                    2  \n",
       "29                    4  \n",
       "...                 ...  \n",
       "17040                 2  \n",
       "17041                 5  \n",
       "17042                 4  \n",
       "17043                 2  \n",
       "17044                 9  \n",
       "17045                 3  \n",
       "17046                 4  \n",
       "17047                 3  \n",
       "17048                 4  \n",
       "17049                 2  \n",
       "17050                 3  \n",
       "17051                 5  \n",
       "17052                 3  \n",
       "17053                 3  \n",
       "17054                 3  \n",
       "17055                 4  \n",
       "17056                 4  \n",
       "17057                 9  \n",
       "17058                 5  \n",
       "17059                 1  \n",
       "17060                 1  \n",
       "17061                 2  \n",
       "17062                 3  \n",
       "17063                 1  \n",
       "17064                 2  \n",
       "17065                 4  \n",
       "17066                 4  \n",
       "17067                 2  \n",
       "17068                 1  \n",
       "17069                 1  \n",
       "\n",
       "[17070 rows x 5 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#creating a metric to see number of competitors for a given project in a given week\n",
    "#number of participants in a given category, that launched in the same year and week and in the same goal bucket\n",
    "ks_particpants_wk=kick.groupby(['main_category','launch_year','launch_week','goal_amount_category_perc']).count()\n",
    "ks_particpants_wk=ks_particpants_wk[['project_name']]\n",
    "#since the above table has all group by columns created as index, converting them into columns\n",
    "ks_particpants_wk.reset_index(inplace=True)\n",
    "ks_particpants_wk.rename(index=str, columns={\"project_name\": \"competitors_week\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.merge(kick, ks_particpants_qtr, on=['main_category','launch_year','launch_quarter','goal_amount_category_perc'], \n",
    "                  how= 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.merge(kick, ks_particpants_mth, on=['main_category','launch_year','launch_month','goal_amount_category_perc'], \n",
    "                  how= 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.merge(kick, ks_particpants_wk, on=['main_category','launch_year','launch_week','goal_amount_category_perc'], \n",
    "                  how= 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "#additional features from goal, pledge and backers columns\n",
    "kick.loc[:,'goal_reached'] = kick['pledged_amount_usd'] / kick['goal_amount_usd'] # Pledged amount as a percentage of goal.\n",
    "#The above field will be used to compute another metric\n",
    "# In backers column, impute 0 with 1 to prevent undefined division.\n",
    "kick.loc[kick['backers_count'] == 0, 'backers_count'] = 1 \n",
    "kick.loc[:,'pledge_per_backer'] = kick['pledged_amount_usd'] / kick['backers_count'] # Pledged amount per backer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "#####################################################################\n",
    "# using these metrics to create AVG_PPB and AVG_SUCCESS_RATE features\n",
    "#####################################################################\n",
    "\n",
    "#creating 2 metrics to get average pledge per backer for a category in a year according to the goal bucket it lies in and the success rate ie average pledged to goal ratio for the category and goal bucket in this year#creati \n",
    "#using pledge_per_backer and goal_reached (computed earlier) and averaging it by category in a launch year belonging to a goal bucket\n",
    "ks_ppb_goal=pd.DataFrame(kick.groupby(['main_category','launch_year','goal_amount_category_perc'])['pledge_per_backer','goal_reached'].mean())\n",
    "#since the above table has all group by columns created as index, converting them into columns\n",
    "ks_ppb_goal.reset_index(inplace=True)\n",
    "#renaming column\n",
    "ks_ppb_goal.columns= ['main_category','launch_year','goal_amount_category_perc','avg_ppb_goal','avg_success_rate_goal']\n",
    "\n",
    "#creating a metric: the success rate ie average pledged to goal ratio for the category in this year according to the duration buckets\n",
    "ks_ppb_duration=pd.DataFrame(kick.groupby(['main_category','launch_year','campaign_length_category_perc'])['goal_reached'].mean())\n",
    "#since the above table has all group by columns created as index, converting them into columns\n",
    "ks_ppb_duration.reset_index(inplace=True)\n",
    "#renaming column\n",
    "ks_ppb_duration.columns= ['main_category','launch_year','campaign_length_category_perc','avg_success_rate_campaign_length']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.merge(kick, ks_ppb_goal, on=['main_category','launch_year','goal_amount_category_perc'], how= 'inner')\n",
    "kick = pd.merge(kick, ks_ppb_duration, on=['main_category','launch_year','campaign_length_category_perc'], how= 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "kick.loc[:,'project_name_words_sd'] = np.sqrt((kick.project_name_words - \n",
    "                                           kick.loc[kick.staff_pick == True].groupby(['staff_pick'])['project_name_words'].mean()[1])**2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick.loc[:,'project_blurb_words_sd'] = np.sqrt((kick.project_blurb_words - \n",
    "                                           kick.loc[kick.staff_pick == True].groupby(['staff_pick'])['project_blurb_words'].mean()[1])**2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick.created_date = pd.to_datetime(kick.created_date)\n",
    "kick.state_changed_date = pd.to_datetime(kick.state_changed_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['days_between_create_and_launch'] = (kick.launched - kick.created_date).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick['days_between_launch_and_state_change'] = (kick.state_changed_date - kick.launched).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick.to_csv(\"C:\\OSU\\Sem3\\BAN 5753\\SAS Symposium\\kick_data_new.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "kick = pd.read_csv(\"C:\\OSU\\Sem3\\BAN 5753\\SAS Symposium\\kick_data_new.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(163452, 50)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kick.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['project_id', 'project_name', 'project_blurb', 'main_category',\n",
       "       'sub_category', 'project_state', 'country', 'deadline', 'launched',\n",
       "       'campaign_length', 'backers_count', 'currency', 'static_usd_rate',\n",
       "       'pledged_amount', 'pledged_amount_usd', 'goal_amount',\n",
       "       'goal_amount_usd', 'staff_pick', 'spotlight', 'disable_communication',\n",
       "       'launch_year', 'launch_month', 'launch_day',\n",
       "       'campaign_less_than_1_month', 'goal_amount_less_than_75',\n",
       "       'project_blurb_length', 'project_name_length', 'project_name_exclaim',\n",
       "       'project_name_question', 'project_name_words', 'project_name_is_upper',\n",
       "       'goal_amount_category_perc', 'campaign_length_category_perc',\n",
       "       'launch_quarter', 'launch_week', 'competitors_quarter',\n",
       "       'competitors_month', 'competitors_week', 'goal_reached',\n",
       "       'pledge_per_backer', 'avg_ppb_goal', 'avg_success_rate_goal',\n",
       "       'avg_success_rate_campaign_length', 'project_blurb_words',\n",
       "       'project_name_words_sd', 'project_blurb_words_sd', 'created_date',\n",
       "       'state_changed_date', 'days_between_create_and_launch',\n",
       "       'days_between_launch_and_state_change'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kick.columns"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
